package com.example.computer_exam_training_sys.mapper;

import com.example.computer_exam_training_sys.vo.ExamPaperAnalysisResult;
import com.example.computer_exam_training_sys.vo.ExamPaperSummary;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ExamAnalysisMapper {
    /**
     * 获取某张试卷中题目表现分析数据（仅低于60分的题目），含分页和建议
     * @param paperId 试卷ID
     * @param offset 分页偏移
     * @param size 每页大小
     * @return 题目分析结果列表
     */
    @Select("<script>" +
            "WITH paper_overall AS ( " +
            "  SELECT " +
            "    AVG(e.user_score) AS avg_paper_score, " +
            "    AVG(e.duration) AS avg_paper_time_spent " +
            "  FROM user_exams e " +
            "  WHERE e.paper_id = #{paperId} " +
            "), question_stats AS ( " +
            "  SELECT " +
            "    d.question_id, " +
            "    AVG(d.is_correct) * 10 AS avg_question_score, " +
            "    CASE " +
            "      WHEN AVG(d.is_correct)*10 >= 8 THEN 3 " +
            "      WHEN AVG(d.is_correct)*10 >= 6 THEN 2 " +
            "      ELSE 1 " +
            "    END AS difficulty " +
            "  FROM user_exam_details d " +
            "  JOIN user_exams e ON d.exam_id = e.exam_id " +
            "  JOIN paper_questions pq ON d.question_id = pq.question_id " +
            "  WHERE e.paper_id = #{paperId} " +
            "  GROUP BY d.question_id " +
            ") " +
            "SELECT " +
            "  po.avg_paper_score, " +
            "  po.avg_paper_time_spent, " +
            "  qs.question_id, " +
            "  qs.avg_question_score, " +
            "  qs.difficulty, " +
            "  CASE " +
            "    WHEN qs.avg_question_score &lt; 6 THEN '建议降低难度' " +
            "    WHEN qs.avg_question_score &gt; 8 THEN '建议提高难度' " +
            "    ELSE '难度适中' " +
            "  END AS difficulty_suggestion " +
            "FROM paper_overall po " +
            "JOIN question_stats qs ON 1=1 " +
            "WHERE qs.avg_question_score &lt; 6 " +
            "ORDER BY qs.avg_question_score ASC " +
            "LIMIT #{offset}, #{size} " +
            "</script>")
    List<ExamPaperAnalysisResult> getExamPaperAnalysis(
            @Param("paperId") Integer paperId,
            @Param("offset") int offset,
            @Param("size") int size);


    /**
     * 获取某张试卷的整体平均得分和平均作答时间
     * @param paperId 试卷ID
     * @return 试卷汇总信息
     */
    @Select("SELECT " +
            "  AVG(e.user_score) AS avg_paper_score, " +
            "  AVG(e.duration) AS avg_paper_time_spent " +
            "FROM user_exams e " +
            "WHERE e.paper_id = #{paperId}")
    ExamPaperSummary getPaperSummary(@Param("paperId") Integer paperId);
}

